"""
Case Type   : 客户场景-邮储
Case Name   : insert 插入values为函数返回值 表达式 及index only scan查询语句支持bypass优化
Create At   : 2021/11/25
Owner       : wan005
Description :
    1.创建表
    2.创建函数
    3.解析插入语句
    4.插入数据
    5.创建索引
    6.解析查询数据且查询条件在索引范围内
    7.解析查询数据查询条件不在索引上
    8.查询数据检查查询结果
Expect      :
    1.创建表成功
    2.创建函数成功
    3.插入语句被优化【Bypass】
    4.插入数据成功
    5.创建索引成功
    6.查询语句被优化【Bypass】
    7.查询语句未被优化【No Bypass】
    8.查询结果正确
History     :
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Finance(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.constant = Constant()
        self.commonsh = CommonSH("PrimaryDbUser")
        status = self.commonsh.get_db_cluster_status("detail")
        self.log.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)
        status = self.commonsh.restart_db_cluster()
        self.log.info(status)
        status = self.commonsh.get_db_cluster_status("detail")
        self.log.info(status)
        self.assertTrue("Normal" in status or "Degraded" in status)

        result = self.commonsh.execut_db_sql("show enable_opfusion")
        self.assertNotIn("ERROR", result)
        self.enable_opfusion = result.splitlines()[-2].strip()
        self.log.info(self.enable_opfusion)
        result = self.commonsh.execut_db_sql("show enable_bitmapscan")
        self.assertNotIn("ERROR", result)
        self.enable_bitmapscan = result.splitlines()[-2].strip()
        self.log.info(self.enable_bitmapscan)
        result = self.commonsh.execut_db_sql("show enable_seqscan")
        self.assertNotIn("ERROR", result)
        self.enable_seqscan = result.splitlines()[-2].strip()
        self.log.info(self.enable_seqscan)
        result = self.commonsh.execut_db_sql("show opfusion_debug_mode")
        self.assertNotIn("ERROR", result)
        self.opfusion_debug_mode = result.splitlines()[-2].strip()
        self.log.info(self.opfusion_debug_mode)

        self.f_name = "f_finance_007"
        self.t_name = "t_finance_007"

    def test_finance(self):
        text = "-----step:修改参数; expect:成功-----"
        self.log.info(text)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          "enable_opfusion=on")
        self.assertTrue(res, "执行失败:" + text)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          "enable_bitmapscan=off")
        self.assertTrue(res, "执行失败:" + text)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          "enable_seqscan=off")
        self.assertTrue(res, "执行失败:" + text)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          "opfusion_debug_mode='log'")
        self.assertTrue(res, "执行失败:" + text)

        text = f"-----step1:创建表; expect:成功-----"
        self.log.info(text)
        sql = f"create table {self.t_name}(c_bit BIT(3) ," \
            "c_int int,c_time time with time zone, " \
            "c_char char default 'c', c_text text);"
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, result,
                      "执行失败:" + text)

        text = "-----step2:创建函数; expect:成功-----"
        self.log.info(text)
        sql = f'''create function {self.f_name}(integer, integer) 
                returns integer
                as 'select \$1 + \$2;'
                language sql
                immutable
                returns null on null input;
                '''
        self.log.info(sql)
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, result,
                      "执行失败:" + text)

        text = "-----step3:解析插入语句; expect:解析结果包含【Bypass】-----"
        self.log.info(text)
        sql = f'''explain verbose insert into 
                {self.t_name} values(B'101'|B'111',
                {self.f_name}(1,1),'21:21:21 pst', default, '哈哈哈');
                explain insert into {self.t_name}(c_text) values('#……￥')'''
        sql_list = sql.split(';')
        for sql in sql_list:
            self.log.info(sql)
            result = self.commonsh.execut_db_sql(sql)
            self.log.info(result)
            self.assertIn("Bypass", result, "执行失败:" + text)

        text = "-----step4:插入数据; expect:成功-----"
        self.log.info(text)
        sql = f'''insert into {self.t_name} 
                values(B'101'|B'111',{self.f_name}(1,1),
                '21:21:21 pst', default, '哈哈哈');
                insert into {self.t_name}(c_text) values('#……￥')'''
        sql_list = sql.split(';')
        for sql in sql_list:
            self.log.info(sql)
            result = self.commonsh.execut_db_sql(sql)
            self.log.info(result)
            self.assertIn(self.constant.INSERT_SUCCESS_MSG, result,
                          "执行失败:" + text)

        text = "-----step5:创建索引; expect:成功-----"
        self.log.info(text)
        sql = f'''create index index_finance_007_01 on {self.t_name}(c_bit);
                create index index_finance_007_02 on {self.t_name}(c_int);
                create index index_finance_007_03 on {self.t_name}(c_time);
                create index index_finance_007_04 on {self.t_name}(c_char);
                create index index_finance_007_05 on {self.t_name}(c_text) 
                where c_text != '哈哈哈';'''
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, result,
                      "执行失败:" + text)
        self.assertNotIn("ERROR", result, "执行失败:" + text)

        text = "-----step6:解析查询数据且查询条件在索引范围内; expect:解析结果包含【Bypass】-----"
        self.log.info(text)
        sql = f'''explain verbose select * from {self.t_name} 
                where c_time='21:21:21 pst';
                explain select * from {self.t_name} where c_bit=B'101';
                explain select * from {self.t_name} where c_int=2;
                explain select * from {self.t_name} where c_char=\'c\''''
        sql_list = sql.split(';')
        for sql in sql_list:
            self.log.info(sql)
            result = self.commonsh.execut_db_sql(sql)
            self.log.info(result)
            self.assertIn("Bypass", result, "执行失败:" + text)

        text = "-----step7:解析查询数据查询条件不在索引上; expect:解析结果包含【No Bypass】-----"
        self.log.info(text)
        sql = f'''explain verbose select * from {self.t_name} 
            where c_text='哈哈哈';'''
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn("No Bypass", result, "执行失败:" + text)

        text = "-----step8:查询数据检查查询结果; expect:查询结果正确-----"
        self.log.info(text)
        sql = f"select * from {self.t_name} where c_bit=B'101';" \
            f"select * from {self.t_name} where c_time='21:21:21 pst';" \
            f"select * from {self.t_name} where c_int=2;" \
            f"select * from {self.t_name} where c_text='哈哈哈';" \
            f"select * from {self.t_name} where c_char='c'"
        sql_list = sql.split(';')
        for i in range(5):
            result = self.commonsh.execut_db_sql(sql)
            self.log.info(result)
            self.assertNotIn("ERROR", result, "执行失败:" + text)
            if i == 0:
                self.assertIn("0 rows", result, "执行失败:" + text)
            elif i >= 1:
                self.assertIn("哈哈哈", result, "执行失败:" + text)
                self.assertIn("21:21:21-08", result, "执行失败:" + text)
                self.assertIn("111", result, "执行失败:" + text)
                self.assertIn("2", result, "执行失败:" + text)
                self.assertIn("c", result, "执行失败:" + text)
            elif i == 0:
                self.assertIn("#……￥", result, "执行失败:" + text)
                self.assertIn("2 rows", result, "执行失败:" + text)

    def tearDown(self):
        text = "-----step9:清理环境; expect:成功-----"
        self.log.info(text)
        result_list = []
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"enable_opfusion="
                                          f"{self.enable_opfusion}")
        result_list.append(res)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"enable_bitmapscan="
                                          f"{self.enable_bitmapscan}")
        result_list.append(res)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"enable_seqscan="
                                          f"{self.enable_seqscan}")
        result_list.append(res)
        res = self.commonsh.execute_gsguc("reload",
                                          self.constant.GSGUC_SUCCESS_MSG,
                                          f"opfusion_debug_mode="
                                          f"{self.opfusion_debug_mode}")
        result_list.append(res)

        sql = f"drop function {self.f_name}(integer, integer);" \
            f"drop table {self.t_name} cascade"
        result = self.commonsh.execut_db_sql(sql)
        self.log.info(result)
        status = self.commonsh.get_db_cluster_status("detail")
        self.log.info(status)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result,
                      "执行失败:" + text)
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG, result,
                      "执行失败:" + text)
        for res in result_list:
            self.assertTrue(res, "执行失败:" + text)
        self.assertTrue("Normal" in status or "Degraded" in status)
        self.log.info(f"-----{os.path.basename(__file__)} finish=")
